﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using ModelLayer;

namespace DBLayer
{
    public class DBPerson
    {
        private SqlConnection con;
        public DBPerson()
        {
            con = DBConnection.getInstance().getDBCon();
        }

        public Customer GetCustomerByPhoneNo(string phoneNo)
        {
            Customer cus = null;

            SqlDataReader reader = null;

            SqlParameter phoneParam = new SqlParameter("@phoneNo", SqlDbType.VarChar);
            phoneParam.Value = phoneNo;

            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandText = "SELECT p.cpr, p.address, p.phoneNo, p.firstName, p.lastName, p.email, c.cityName, p.postalCode_Id FROM Person as p " +
                                "JOIN Customer as cus ON p.cpr = cus.cust_Id " +
                                "JOIN City as c ON p.postalCode_Id = c.postalCodeID " +
                                "WHERE p.phoneNo = @phoneNo ";

            com.Parameters.Add(phoneParam);

            try
            {
                reader = com.ExecuteReader();

                while (reader.Read())
                {
                    cus = new Customer(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6), reader.GetInt32(7));
                }
                reader.Close();
            }
            catch (SqlException e)
            {
                Console.WriteLine(e);
            }
            return cus;
        }

        public Employee GetEmployeeById(int empId)
        {
            Employee emp = null;

            SqlDataReader reader = null;

            SqlParameter idParam = new SqlParameter("@emp_Id", SqlDbType.Int);
            idParam.Value = empId;

            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandText = "SELECT p.cpr, p.address, p.phoneNo, p.firstName, p.lastName, p.email, c.cityName, p.postalCode_Id, emp.emp_Id, emp.title FROM Person as p " +
                                "JOIN Employee as emp ON p.cpr = emp.emp_Id " +
                                "JOIN City as c ON p.postalCode_Id = c.postalCodeID " +
                                "WHERE emp.emp_Id = @emp_Id ";

            com.Parameters.Add(idParam);

            try
            {
                reader = com.ExecuteReader();

                while (reader.Read())
                {
                    emp = new Employee(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6), reader.GetInt32(7), reader.GetInt32(8), reader.GetString(9));
                }
                reader.Close();
            }
            catch (SqlException e)
            {
                Console.WriteLine(e);
            }
            return emp;
        }
    }
}
